package com.dongdongqiang.daogenerator.utils;


import com.dongdongqiang.daogenerator.annotation.ColumnDes;
import com.dongdongqiang.daogenerator.enums.JdbcTypeEnums;
import com.dongdongqiang.daogenerator.exception.TableNotFoundException;
import com.dongdongqiang.daogenerator.pojo.TableColumn;
import com.dongdongqiang.daocomparator.pojo.DbTableMetaInfoVO;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.*;
import java.util.*;

/**
 * 数据库工具 Created by zhangzhenbin on 17-2-23.
 */
public class DatabaseUtils {
    private static final Logger logger = LoggerFactory.getLogger(DatabaseUtils.class);
    private static final String SQLSERVER_DEFAULT_SCHEMA = "dbo";
    private static final String POSTGRES_DEFAULT_SCHEMA = "public";
    private static final String SQLServerTableMetaSQL =
            "SELECT\n"
                    + "  sys.columns.name,\n"
                    + "  sys.types.name as typename,\n"
                    + "  sys.columns.max_length as maxlength,\n"
                    + "  sys.columns.precision,\n"
                    + "  sys.columns.scale,\n"
                    + "  sys.columns.is_nullable as isnullable\n"
                    + "FROM sys.columns, sys.tables, sys.types ,sys.schemas \n"
                    + "WHERE sys.columns.object_id = sys.tables.object_id AND sys.columns.user_type_id = sys.types.user_type_id AND sys.schemas.schema_id = sys.tables.schema_id\n"
                    + "      and sys.tables.name = '{tableName}'\n"
                    + " {schemaSql} "
                    + "ORDER BY sys.columns.column_id asc;";
    private static final String SQLServerTableMetaSchemaWhereSQL =
            "and sys.schemas.name = '{tableSchema}'";

    private static final String PostgresTableMetaSQL =
            "SELECT\n"
                    + "  column_name              AS name,\n"
                    + "  udt_name                 AS typename,\n"
                    + "  character_maximum_length AS maxlength,\n"
                    + "  numeric_precision        AS precision,\n"
                    + "  numeric_scale  AS scale,\n"
                    + "  is_nullable              AS isnullable\n"
                    + "FROM information_schema.\"columns\"\n"
                    + "WHERE \"table_name\" = lower('{tableName}')\n "
                    + " {schemaSql} "
                    + "ORDER BY ordinal_position asc";
    private static final String PostgresTableMetaSchemaWhereSQL =
            "and \"table_schema\" = lower('{tableSchema}')";
    private static final String POSTGRES_ADD_COLUMN_SQL =
            "ALTER TABLE {tableName} ADD {column} {columnType} {nullable} {defaultValue};";
    private static final String POSTGRES_CREATE_TABLE_SQL =
            "CREATE TABLE {tableName} (   {columns}   {primarySql} );";
    private static final String POSTGRES_CREATE_TABLE_PRIMARYKEY_SQL =
            "constraint PK_T_{random}_{tableName} primary key (\"{primaryKey}\")";

    private static final String MysqlTableMetaSQL =
            "select\n"
                    + "  `COLUMN_NAME` as name,\n"
                    + "  DATA_TYPE as typename,\n"
                    + "  CHARACTER_MAXIMUM_LENGTH as maxlength,\n"
                    + "  CHARACTER_OCTET_LENGTH as `precision`,\n"
                    + "  NUMERIC_PRECISION as scale,\n"
                    + "  if(IS_NULLABLE = 'YES',true,false) as isnullable\n"
                    + "from information_schema.columns\n"
                    + "where table_schema = '{dbName}'\n"
                    + "      and table_name = '{tableName}';\n";
    private static final String MYSQL_CREATE_TABLE_SQL =
            "CREATE TABLE {tableName} (   {columns}   {primarySql} );";
    private static final String MYSQL_CREATE_TABLE_PRIMARYKEY_SQL = "primary key ({primaryKey})";
    private static final String MYSQL_ADD_COLUMN_SQL =
            "ALTER TABLE {tableName} ADD {column} {columnType} {nullable}  {defaultValue};";


    /**
     * 获取表结构
     */
    public static List<DbTableMetaInfoVO> getTableMetas(
            Connection connection, String tableName, String primaryKey) throws TableNotFoundException {
        boolean tableExist = verifyDatabaseTableExist(connection, tableName);
        if (!tableExist) {
            throw new TableNotFoundException(tableName + "表未找到");
        }

        String sql = "";
        List<DbTableMetaInfoVO> list = new ArrayList<>();
        String databaseProductName = null;
        DbTableMetaInfoVO tmiv = null;
        try {
            databaseProductName = connection.getMetaData().getDatabaseProductName();
            sql = buildTableMetaSQL(connection, tableName);
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                tmiv = new DbTableMetaInfoVO();
                tmiv.setName(rs.getString("name"));
                tmiv.setScale(rs.getLong("scale"));
                tmiv.setTypeName(rs.getString("typename"));
                tmiv.setPrecision(rs.getLong("precision"));
                tmiv.setNullable(rs.getBoolean("isnullable"));
                tmiv.setMaxLength(rs.getLong("maxlength"));
                tmiv.setPrimaryKey(primaryKey.toLowerCase().equals(tmiv.getName().toLowerCase()));
                if ("Microsoft SQL Server".equals(databaseProductName)) {
                    tmiv.setTypeFullName(
                            buildSqlServerFullTypeName(
                                    tmiv.getTypeName(), tmiv.getMaxLength(), tmiv.getPrecision(), tmiv.getScale()));
                } else if ("PostgreSQL".equals(databaseProductName)) {
                    tmiv.setTypeFullName(
                            buildPostgresFullTypeName(
                                    tmiv.getTypeName(), tmiv.getMaxLength(), tmiv.getPrecision(), tmiv.getScale()));
                } else if ("MySQL".equals(databaseProductName)) {
                    tmiv.setTypeFullName(
                            buildMysqlFullTypeName(
                                    tmiv.getTypeName(), tmiv.getMaxLength(), tmiv.getPrecision(), tmiv.getScale()));
                }
                list.add(tmiv);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 根据metaData获取表结构查询语句
     */
    private static String buildTableMetaSQL(Connection connection, String tableName)
            throws SQLException {
        DatabaseMetaData metaData = connection.getMetaData();
        String databaseProductName = metaData.getDatabaseProductName();
        String sql = "";
        if ("Microsoft SQL Server".equals(databaseProductName)) { // SQLSERVER数据库
            String[] tableNameArray = tableName.split("\\.");
            if (ArrayUtils.getLength(tableNameArray) < 2) {
                String schemaWhereSQL =
                        SQLServerTableMetaSchemaWhereSQL.replace("{tableSchema}", SQLSERVER_DEFAULT_SCHEMA);
                sql =
                        SQLServerTableMetaSQL.replace("{tableName}", tableName)
                                .replace("{schemaSql}", schemaWhereSQL);
            } else {
                String schemaWhereSQL =
                        SQLServerTableMetaSchemaWhereSQL.replace(
                                "{tableSchema}", tableNameArray[tableNameArray.length - 2]);
                sql =
                        SQLServerTableMetaSQL.replace("{tableName}", tableNameArray[tableNameArray.length - 1])
                                .replace("{schemaSql}", schemaWhereSQL);
            }
        } else if ("MySQL".equals(databaseProductName)) { // MYSQL数据库
            String dbName = connection.getCatalog();
            sql = MysqlTableMetaSQL.replace("{tableName}", tableName).replace("{dbName}", dbName);
        } else if ("PostgreSQL".equals(databaseProductName)) { // greenplum
            String[] tableNameArray = tableName.split("\\.");
            if (ArrayUtils.getLength(tableNameArray) < 2) {
                String schemaWhereSQL =
                        PostgresTableMetaSchemaWhereSQL.replace("{tableSchema}", POSTGRES_DEFAULT_SCHEMA);
                sql =
                        PostgresTableMetaSQL.replace("{tableName}", tableName)
                                .replace("{schemaSql}", schemaWhereSQL);
            } else {
                String schemaWhereSQL =
                        PostgresTableMetaSchemaWhereSQL.replace(
                                "{tableSchema}", tableNameArray[tableNameArray.length - 2]);
                sql =
                        PostgresTableMetaSQL.replace("{tableName}", tableNameArray[tableNameArray.length - 1])
                                .replace("{schemaSql}", schemaWhereSQL);
            }
        }
        logger.debug("{buildTableMetaSQL}-输出：\n" + sql);
        return sql;
    }


    /**
     * 构建mysql的DDL语句
     */
    private static String buildMysqlAddColumnDDL(
            String tableName, DbTableMetaInfoVO sourceDbTableMetaInfoVO) {
        String nullableString = sourceDbTableMetaInfoVO.getNullable() ? "NULL" : "NOT NULL";
        String mysqlFullType = sourceDbTableMetaInfoVO.getTypeFullName();
        String defaultValue = sourceDbTableMetaInfoVO.getDefaultValue() == null ? "DEFAULT NULL" : "DEFAULT "+sourceDbTableMetaInfoVO.getDefaultValue();
        return MYSQL_ADD_COLUMN_SQL
                .replace("{tableName}", tableName)
                .replace("{column}", sourceDbTableMetaInfoVO.getName())
                .replace("{columnType}", mysqlFullType)
                .replace("{nullable}", nullableString)
                .replace("{defaultValue}", defaultValue)
                ;
    }

    private static String buildMysqlCreateTableDDL(
            String tableName, List<DbTableMetaInfoVO> dbTableMetaInfoVOS) {
        StringBuilder columnDDL = new StringBuilder();
        String primaryKeyColumn = null;
        for (DbTableMetaInfoVO dbTableMetaInfoVO : dbTableMetaInfoVOS) {
            String nullableString = dbTableMetaInfoVO.getNullable() ? "NULL" : "NOT NULL";
            String defaultValue = dbTableMetaInfoVO.getDefaultValue() == null ? "DEFAULT NULL" : "DEFAULT "+dbTableMetaInfoVO.getDefaultValue();
            String mysqlFullType =
                    buildMysqlFullTypeName(
                            dbTableMetaInfoVO.getTypeName(),
                            dbTableMetaInfoVO.getMaxLength(),
                            dbTableMetaInfoVO.getPrecision(),
                            dbTableMetaInfoVO.getScale());
            columnDDL
                    .append(" `")
                    .append(dbTableMetaInfoVO.getName())
                    .append("` ")
                    .append(mysqlFullType)
                    .append(" ")
                    .append(nullableString)
                    .append(" ")
                    .append(defaultValue)
                    .append(",");
            if (Misc.isTrue(dbTableMetaInfoVO.getPrimaryKey()) && primaryKeyColumn == null) {
                primaryKeyColumn = dbTableMetaInfoVO.getName();
            }
        }
        String primarySql = "";
        if (primaryKeyColumn != null) {
            primarySql = MYSQL_CREATE_TABLE_PRIMARYKEY_SQL.replace("{primaryKey}", primaryKeyColumn);
        } else if (columnDDL.length() > 0) {
            columnDDL.deleteCharAt(columnDDL.length() - 1);
        }
        return MYSQL_CREATE_TABLE_SQL
                .replace("{tableName}", tableName)
                .replace("{columns}", columnDDL)
                .replace("{primarySql}", primarySql);
    }

    /**
     * 构建postgres的DDL语句
     */
    private static String buildPostgresAddColumnDDL(
            String tableName, DbTableMetaInfoVO sourceDbTableMetaInfoVO) {
        String nullableString = sourceDbTableMetaInfoVO.getNullable() ? "NULL" : "NOT NULL";
        String defaultValue = sourceDbTableMetaInfoVO.getDefaultValue() == null ? "DEFAULT NULL" : "DEFAULT "+sourceDbTableMetaInfoVO.getDefaultValue();
        String postgresFullType =
                buildPostgresFullTypeName(
                        sourceDbTableMetaInfoVO.getTypeName(),
                        sourceDbTableMetaInfoVO.getMaxLength(),
                        sourceDbTableMetaInfoVO.getPrecision(),
                        sourceDbTableMetaInfoVO.getScale());
        return POSTGRES_ADD_COLUMN_SQL
                .replace("{tableName}", tableName)
                .replace("{column}", sourceDbTableMetaInfoVO.getName().toLowerCase())
                .replace("{columnType}", postgresFullType)
                .replace("{nullable}", nullableString)
                .replace("{defaultValue}", defaultValue);
    }


    private static String buildPostgresCreateTableDDL(
            String tableName, List<DbTableMetaInfoVO> dbTableMetaInfoVOS) {
        StringBuilder columnDDL = new StringBuilder();
        String primaryKeyColumn = null;
        for (DbTableMetaInfoVO dbTableMetaInfoVO : dbTableMetaInfoVOS) {
            String nullableString = dbTableMetaInfoVO.getNullable() ? "NULL" : "NOT NULL";
            String defaultValue = dbTableMetaInfoVO.getDefaultValue() == null ? "DEFAULT NULL" : "DEFAULT "+dbTableMetaInfoVO.getDefaultValue();
            String postgresFullType =
                    buildPostgresFullTypeName(
                            dbTableMetaInfoVO.getTypeName(),
                            dbTableMetaInfoVO.getMaxLength(),
                            dbTableMetaInfoVO.getPrecision(),
                            dbTableMetaInfoVO.getScale());
            columnDDL
                    .append(" \"")
                    .append(dbTableMetaInfoVO.getName().toLowerCase())
                    .append("\" ")
                    .append(postgresFullType)
                    .append(" ")
                    .append(nullableString)
                    .append(" ")
                    .append(defaultValue)
                    .append(",");
            if (Misc.isTrue(dbTableMetaInfoVO.getPrimaryKey()) && primaryKeyColumn == null) {
                primaryKeyColumn = dbTableMetaInfoVO.getName();
            }
        }
        String primarySql = "";
        if (primaryKeyColumn != null) {
            primarySql =
                    POSTGRES_CREATE_TABLE_PRIMARYKEY_SQL
                            .replace("{random}", Misc.getRandomString(10))
                            .replace("{tableName}", tableName.replace(".", "_"))
                            .replace("{primaryKey}", primaryKeyColumn.toLowerCase());
        } else if (columnDDL.length() > 0) {
            columnDDL.deleteCharAt(columnDDL.length() - 1);
        }
        return POSTGRES_CREATE_TABLE_SQL
                .replace("{tableName}", tableName)
                .replace("{columns}", columnDDL)
                .replace("{primarySql}", primarySql);
    }

    /**
     * 获取字段类型全名
     */
    private static String buildPostgresFullTypeName(
            String typeName, Long maxLength, Long precision, Long scale) {
        if ("bpchar".equals(typeName.toLowerCase())) {
            typeName = "char";
        }
        if ("char".equals(typeName.toLowerCase()) || "varchar".equals(typeName.toLowerCase()))
            if (maxLength != null && maxLength != 0) {
                typeName = typeName + "(" + maxLength + ")";
            }
        if ("numeric".equals(typeName.toLowerCase()))
            if (precision != null && precision != 0) {
                if (scale != null && scale != 0) {
                    typeName = typeName + "(" + precision + "," + scale + ")";
                } else {
                    typeName = typeName + "(" + precision + ")";
                }
            }


        return typeName;
    }

    public static String buildMysqlFullTypeName(
            String typeName, Long maxLength, Long precision, Long scale) {
        if ("char".equals(typeName.toLowerCase()) || "varchar".equals(typeName.toLowerCase()))
            if (maxLength != null && maxLength != 0) {
                typeName = typeName + "(" + maxLength + ")";
            }
        if ("decimal".equals(typeName.toLowerCase()))
            if (precision != null && precision != 0) {
                if (scale != null && scale != 0) {
                    typeName = typeName + "(" + precision + "," + scale + ")";
                } else {
                    typeName = typeName + "(" + precision + ")";
                }
            }
        return typeName;
    }

    public static String buildSqlServerFullTypeName(
            String typeName, Long maxLength, Long precision, Long scale) {
        if ("char".equals(typeName.toLowerCase())
                || "varchar".equals(typeName.toLowerCase())
                || "nvarchar".equals(typeName.toLowerCase()))
            if (maxLength != null && maxLength != 0) {
                typeName = typeName + "(" + maxLength + ")";
            }
        if ("numeric".equals(typeName.toLowerCase()) || "decimal".equals(typeName.toLowerCase()))
            if (precision != null && precision != 0) {
                if (scale != null && scale != 0) {
                    typeName = typeName + "(" + precision + "," + scale + ")";
                } else {
                    typeName = typeName + "(" + precision + ")";
                }
            }
        return typeName;
    }


    public static void executeBatchSQL(Connection connection, List<String> sqls)
            throws SQLException, ClassNotFoundException {
        Statement statement = null;
        try {
            statement = connection.createStatement();
            for (String sql : sqls) {
                logger.info("{" + connection.getMetaData().getURL() + "}-运行SQL:" + sql);
                statement.addBatch(sql);
            }
            statement.executeBatch();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    logger.error(
                            "com.tuandai.rtdp.common.utils.DatabaseUtils.executeBatchSQL关闭statement失败！"
                                    + ExceptionUtils.getStackTrace(e));
                }
            }
        }
    }


    public static boolean verifyDatabaseTableExist(Connection connection, String table) {

        try {
            JdbcTypeEnums jdbcTypeEnumsByUrl = getJdbcTypeEnumsByUrl(connection.getMetaData().getURL());
            String selectCountSql;
            if (JdbcTypeEnums.POSTGRESQL.getValue().equals(jdbcTypeEnumsByUrl.getValue())) {
                selectCountSql = "select * from " + table + " limit 0 offset 1;";
            } else if (JdbcTypeEnums.SQLSERVER.getValue().equals(jdbcTypeEnumsByUrl.getValue())
                    || JdbcTypeEnums.JTDS.getValue().equals(jdbcTypeEnumsByUrl.getValue())) {
                selectCountSql = "select top 1 * from " + table + ";";
            } else {
                selectCountSql = "select * from " + table + " limit 0 , 1;";
            }
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(selectCountSql);
            resultSet.close();
            statement.close();
        } catch (Exception e) {
            return false;
        }
        return true;
    }

    /**
     * 检查表字段是否存在
     */
    public static boolean verifyDatabaseColumnExist(
            Connection connection, final String table, final String column) {
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            String url = connection.getMetaData().getURL();
            JdbcTypeEnums jdbcTypeEnumsByUrl = getJdbcTypeEnumsByUrl(url);
            Class.forName(jdbcTypeEnumsByUrl.getClassName());
            String selectCountSql;
            if (JdbcTypeEnums.MYSQL.getValue().equals(jdbcTypeEnumsByUrl.getValue())) {
                selectCountSql = "select " + column + " from " + table + " limit 0 , 1;";
            } else if (JdbcTypeEnums.POSTGRESQL.getValue().equals(jdbcTypeEnumsByUrl.getValue())) {
                selectCountSql = "select * from " + table + " limit 0 offset 1;";
            } else {
                selectCountSql = "select top 1 " + column + " from " + table + ";";
            }
            statement = connection.createStatement();
            resultSet = statement.executeQuery(selectCountSql);
            return true;
        } catch (Exception e) {
            return false;
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    logger.error(
                            "com.tuandai.rtdp.common.utils.DatabaseUtils.verifyDatabaseColumnExist方法中关闭resultSet失败");
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    logger.error(
                            "com.tuandai.rtdp.common.utils.DatabaseUtils.verifyDatabaseColumnExist方法中关闭statement失败");
                }
            }
        }
    }


    /**
     * 通过url获取jdbc类型
     */
    public static JdbcTypeEnums getJdbcTypeEnumsByUrl(String connectionUrl) {
        if (connectionUrl.contains("jtds")) {
            return JdbcTypeEnums.JTDS;
        }
        if (connectionUrl.contains("sqlserver")) {
            return JdbcTypeEnums.SQLSERVER;
        }
        if (connectionUrl.contains("mysql")) {
            return JdbcTypeEnums.MYSQL;
        }
        if (connectionUrl.contains("postgresql")) {
            return JdbcTypeEnums.POSTGRESQL;
        }
        return null;
    }


    public static class SQLQueryResult {
        private List<LinkedHashMap<String, String>> results;
        private long execTime;
        private long size;

        public List<LinkedHashMap<String, String>> getResults() {
            return results;
        }

        public void setResults(List<LinkedHashMap<String, String>> results) {
            this.results = results;
        }

        public long getExecTime() {
            return execTime;
        }

        public void setExecTime(long execTime) {
            this.execTime = execTime;
        }

        public long getSize() {
            return size;
        }

        public void setSize(long size) {
            this.size = size;
        }
    }

    public static String createTableDDL(String tableName, Connection connection, List<DbTableMetaInfoVO> dbTableMetaInfoVOS) throws SQLException {
        JdbcTypeEnums jdbcType = getJdbcTypeEnumsByUrl(connection.getMetaData().getURL());
        if (JdbcTypeEnums.POSTGRESQL.equals(jdbcType)) {
            return buildPostgresCreateTableDDL(tableName, dbTableMetaInfoVOS);
        } else if (JdbcTypeEnums.MYSQL.equals(jdbcType)) {
            return buildMysqlCreateTableDDL(tableName, dbTableMetaInfoVOS);
        }
        return null;
    }

}
